﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Reflection;
using System.Text;
using Microsoft.Office.Interop.Excel;
using ReflectionUtilHelpV2;

namespace OfficeUtilHelpV2
{
    public class ExcelToolV2
    {

        public static void ToExcel<T>(IDictionary<string, IList<T>> exceldbSource, string savePath) where T : class
        {
            if (string.IsNullOrEmpty(savePath) || !savePath.ToLower().Contains(""))
                throw new ArgumentNullException("savePath");
            if (exceldbSource == null)
                throw new ArgumentNullException("exceldbSource");

            #region 声明
            object missing = Missing.Value;
            Application _excelApp = null;
            Workbooks _books = null;
            _Workbook _book = null;
            Sheets _sheets = null;
            _Worksheet _sheet = null;
            Range _range = null;
            Microsoft.Office.Interop.Excel.Font font = null;
            object optionalValue = Missing.Value;
            string strHeaderStart = "A1";
            string strDataStart = "A2";
            #endregion

            try
            {
                #region 初始化
                _excelApp = new Microsoft.Office.Interop.Excel.Application();
                _books = (Microsoft.Office.Interop.Excel.Workbooks)_excelApp.Workbooks;
                _book = (Microsoft.Office.Interop.Excel._Workbook)(_books.Add(optionalValue));
                _sheets = (Microsoft.Office.Interop.Excel.Sheets)_book.Worksheets;
                #endregion
                int _excelSheetCnt = exceldbSource.Count;
                foreach (KeyValuePair<string, IList<T>> db in exceldbSource)
                {
                    _sheet = (Microsoft.Office.Interop.Excel._Worksheet)(_sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing));
                    _sheet.Name = db.Key;
                    #region 创建EXCEL表头
                    IDictionary<string, string> _headers = ReflectionToolV2.GetDisplayName<T>();
                    int _headerColCnt = _headers.Count;
                    _range = _sheet.get_Range(strHeaderStart, optionalValue);
                    _range = _range.get_Resize(1, _headerColCnt);
                    string[] _headerDisplayNames = new string[_headerColCnt];
                    _headers.Values.CopyTo(_headerDisplayNames, 0);
                    _range.set_Value(optionalValue, _headerDisplayNames);
                    _range.BorderAround(Type.Missing, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Type.Missing);
                    font = _range.Font;
                    font.Bold = true;
                    _range.Interior.Color = Color.LightGray.ToArgb();
                    #endregion
                    #region 获取集合中数据进行填充
                    IList<T> _list = db.Value;
                    int _dbCnt = _list.Count;
                    object[,] objData = new object[_dbCnt, _headers.Count];
                    for (int j = 0; j < _dbCnt; j++)
                    {
                        var _item = _list[j];
                        int i = 0;
                        foreach (KeyValuePair<string, string> entry in _headers)
                        {
                            var y = typeof(T).InvokeMember(entry.Key, BindingFlags.GetProperty, null, _item, null);
                            objData[j, i++] = (y == null) ? "" : y.ToString();
                        }
                    }
                    _range = _sheet.get_Range(strDataStart, optionalValue);
                    _range = _range.get_Resize(_dbCnt, _headers.Count);
                    _range.set_Value(optionalValue, objData);
                    _range.BorderAround(Type.Missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
                    _range = _sheet.get_Range(strHeaderStart, optionalValue);
                    _range = _range.get_Resize(_dbCnt + 1, _headers.Count);
                    _range.Columns.AutoFit();
                    #endregion
                }
                #region 删除预先的sheet,并保存数据
                int nSheet = _book.Worksheets.Count;
                if (nSheet > _excelSheetCnt)
                {
                    while (nSheet > _excelSheetCnt)
                    {
                        ((Worksheet)_book.Worksheets[nSheet]).Delete();
                        nSheet--;
                    }

                }
                if (!string.IsNullOrEmpty(savePath))
                    // url:http://msdn.microsoft.com/zh-cn/library/microsoft.office.tools.excel.workbook.saveas.aspx
                    _book.SaveAs(savePath, missing, missing, missing, false, false, XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
                _excelApp.Quit();
                #endregion
                #region 释放资源
                try
                {
                    if (_sheet != null)
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(_sheet);
                    _sheet = null;
                    if (_sheets != null)
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(_sheets);
                    _sheets = null;
                    if (_book != null)
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(_book);
                    _book = null;
                    if (_books != null)
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(_books);
                    _books = null;
                    if (_excelApp != null)
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(_excelApp);
                    _excelApp = null;
                }
                catch
                {
                    _sheet = null;
                    _sheets = null;
                    _book = null;
                    _books = null;
                    _excelApp = null;
                }
                finally
                {
                    GC.Collect();
                }

                #endregion
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("ToExcel Exception:{0}", ex.Message.Trim()));
            }
        }
    }
}
